import sqlite3 from 'sqlite3';
import { fileURLToPath } from 'url';
import path from 'path';
import { ResultData } from './result.js';
// 获取当前文件的绝对路径
const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

// 连接到SQLite数据库
const db = new sqlite3.Database(path.join(__dirname, '../database.sqlite'), (err) => {
  if (err) {
    console.error('Error connecting to database:', err.message);
  } else {
    console.log('Connected to the SQLite database.');
  }
});

// 初始化数据库表
db.serialize(() => {
  const createTables = `
    CREATE TABLE IF NOT EXISTS students (
        StudentID INTEGER PRIMARY KEY AUTOINCREMENT,
        Name VARCHAR(20),
        Email VARCHAR(30),
        Password VARCHAR(30),
        Photo VARCHAR(20),
        Bio VARCHAR(255),
        Interests TEXT,
        Courses VARCHAR(255),
        Achievements VARCHAR(255),
        IsDeleted BOOLEAN DEFAULT 0
    );

    CREATE TABLE IF NOT EXISTS groups (
        GroupID INTEGER PRIMARY KEY AUTOINCREMENT,
        GroupName TEXT NOT NULL,
        Description TEXT,
        Type TEXT,
        AdminID INT NOT NULL,
        Privacy TEXT,
        IsDeleted BOOLEAN DEFAULT 0,
        FOREIGN KEY (AdminID) REFERENCES students (StudentID)
    );

    CREATE TABLE IF NOT EXISTS members (
        GroupID INTEGER,
        StudentID INT,
        JoinDate DATETIME,
        IsDeleted BOOLEAN DEFAULT 0,
        FOREIGN KEY (GroupID) REFERENCES groups,
        FOREIGN KEY (StudentID) REFERENCES students(StudentID)
    );

    CREATE TABLE IF NOT EXISTS tags (
        TagID INTEGER PRIMARY KEY AUTOINCREMENT,
        Type INT,  -- 1: post 2: activity
        Name VARCHAR(20),
        IsDeleted BOOLEAN DEFAULT 0
    );

    CREATE TABLE IF NOT EXISTS noticeboardPosts (
        PostID INTEGER PRIMARY KEY AUTOINCREMENT,
        Title VARCHAR(100) NOT NULL,
        Content TEXT NOT NULL,
        StudentID INTEGER NOT NULL,
        TagID INTEGER,
        CreateTime DATETIME DEFAULT CURRENT_TIMESTAMP,
        UpdateTime DATETIME DEFAULT CURRENT_TIMESTAMP,
        LikeCount INTEGER DEFAULT 0,
        CommentCount INTEGER DEFAULT 0,
        IsHot BOOLEAN DEFAULT 0,
        IsDeleted BOOLEAN DEFAULT 0,
        Media TEXT,
        FOREIGN KEY (StudentID) REFERENCES students(StudentID),
        FOREIGN KEY (TagID) REFERENCES tags(TagID)
    );

    CREATE TABLE IF NOT EXISTS likes (
        PostID INTEGER,
        StudentID INTEGER,
        CreateTime DATETIME DEFAULT CURRENT_TIMESTAMP,
        IsDeleted BOOLEAN DEFAULT 0,
        PRIMARY KEY (PostID, StudentID),
        FOREIGN KEY (PostID) REFERENCES noticeboardPosts(PostID),
        FOREIGN KEY (StudentID) REFERENCES students(StudentID)
    );

    CREATE TABLE IF NOT EXISTS comments (
        CommentID INTEGER PRIMARY KEY AUTOINCREMENT,
        PostID INTEGER,
        StudentID INTEGER,
        Content TEXT NOT NULL,
        CreateTime DATETIME DEFAULT CURRENT_TIMESTAMP,
        UpdateTime DATETIME DEFAULT CURRENT_TIMESTAMP,
        IsDeleted BOOLEAN DEFAULT 0,
        FOREIGN KEY (PostID) REFERENCES noticeboardPosts(PostID),
        FOREIGN KEY (StudentID) REFERENCES students(StudentID)
    );
  `;

  db.exec(createTables, (err) => {
    if (err) {
      console.error('Error creating tables:', err.message);
    } else {
      console.log('Tables created successfully.');
    }
  });
});

// 枚举对象：定义数据库操作类型
export const DB_METHOD = Object.freeze({
  ALL: 'all',
  RUN: 'run',
  GET: 'get',
});

class SqlExecUtil {
  exec = (method, query, params = []) => {
    return new Promise((resolve, reject) => {
      try {
        console.log('Executing SQL:', query, params);
        db[method](query, params, (err, result) => {
          if (err) {
            console.error('Database query error:', err);
            reject(ResultData.error(err));
          } else {
            console.log('Query result:', result);
            resolve(result);
          }
        });
      } catch (err) {
        console.error('Unexpected error in sqlExecUtil:', err);
        reject(ResultData.error(err));
      }
    });
  };
  
  async all(query, params) {
    return await this.exec(DB_METHOD.ALL, query, params);
  }
  
  async getLimit(query, params, page = 1) {
    query += ' LIMIT ? OFFSET ?';
    const limit = 20;
    const offset = (page - 1) * limit;
    const list = await this.exec(DB_METHOD.ALL, query, [...params, limit, offset]);
    return {list, page: parseInt(page), limit};
  }

  async run(query, params) {
    return await this.exec(DB_METHOD.RUN, query, params);
  }

  async get(query, params) {
    return await this.exec(DB_METHOD.GET, query, params);
  }

  async beginTransaction() {
    return new Promise((resolve, reject) => {
      db.run('BEGIN TRANSACTION', (err) => {
        if (err) reject(err);
        else resolve();
      });
    });
  }

  async commit() {
    return new Promise((resolve, reject) => {
      db.run('COMMIT', (err) => {
        if (err) reject(err);
        else resolve();
      });
    });
  }

  async rollback() {
    return new Promise((resolve, reject) => {
      db.run('ROLLBACK', (err) => {
        if (err) reject(err);
        else resolve();
      });
    });
  }

  // 封装事务方法，用于在事务中执行操作，保证操作的原子性
  async withTransaction(callback) {
    try {
      await this.beginTransaction();
      const result = await callback();
      await this.commit();
      return result;
    } catch (error) {
      await this.rollback();
      throw error;
    }
  }
}

export const sqlUtil = new SqlExecUtil();
export default db;